/*
 * File    : /part1/model.query.js
 * Time    : 2021-03-21 20:48:41
 * Author  : Gray Snail
 * Version : 1.0
 * Contact : shtao2011@sina.com
 * License : Copyright (c) 2021 Gray Snail
 * Desc    : None
 */

const { Sequelize, Model, DataTypes, Op } = require('sequelize')

const sequelize = new Sequelize('sqlite::memory:')

class User extends Model {}

User.init(
    {
        username: {
            type: DataTypes.STRING(32),
            allowNull: false,
        },
        isAdmin: {
            type: DataTypes.BOOLEAN,
            defaultValue: false,
        },
    },
    { sequelize, modelName: 'User', tableName: 'user' },
)

async function main() {
    await sequelize.sync({ force: true })

    /***create field  ***/
    const user = await User.create(
        { username: 'alice123', isAdmin: true },
        { fields: ['username'] },
    )
    await User.create({ username: 'admin', isAdmin: true })

    console.log(user.toJSON())
    console.log(user.isAdmin) // undefined

    /******** attributes ******/
    const users = await User.findAll()
    console.log(users.every((user) => user instanceof User))
    console.log('All users:', JSON.stringify(users, null, 2))

    const users1 = await User.findAll({
        attributes: ['id', ['username', 'name']],
    })
    console.log('All users:', JSON.stringify(users1, null, 2))

    const users2 = await User.findAll({
        attributes: {
            include: [[sequelize.fn('COUNT', sequelize.col('id')), 'count']],
        },
    })
    console.log(JSON.stringify(users2, null, 1))

    const users3 = await User.findAll({
        attributes: { exclude: ['updatedAt'] },
    })
    console.log(JSON.stringify(users3, null, 1))

    /******** where, Op ******/
    // Op包含了SQL查询中所有的条件语句
    const users4 = await User.findAll({
        where: {
            username: 'admin',
            // [Op.eq]: {
            //     isAdmin: true,
            // },
            // [Op.or]: [{ isAdmin: true }, { isAdmin: false }],
            // [Op.like]: { username: 'a' },
        },
    })

    console.log(JSON.stringify(users4))

    //  Model.bulkCreate 创建多个实例对象
    //  validate:true 开启字段验证，默认关闭
    // User.bulkCreate([], {})

    // 排序 order 和分组 group
    // 分页 limit offset

    /************** Utility methods *********** */
    // User.count()
    // User.max()
    // User.min()
    // User.sum()

    /************ Finders ********/
    const userF1 = await User.findByPk('1')
    const userF2 = await User.findOne({ where: { username: 'admin' } })

    const [userF3, created] = await User.findOrCreate({
        where: {
            username: 'li si',
        },
        defaults: {
            isAdmin: true,
        },
    })
    console.log(userF3.toJSON(), created)

    const [userF4, build] = await User.findOrBuild({
        where: {
            username: 'zhang three',
        },
        defaults: {
            isAdmin: true,
        },
    })
    console.log(userF4.toJSON(), build)

    const { count: count1, rows: userF5 } = await User.findAndCountAll({
        where: {
            isAdmin: true,
        },
    })
    console.log(count1, JSON.stringify(userF5))
}

main()
